﻿CREATE TABLE [dbo].[ToolFile] AS FILETABLE FILESTREAM_ON [FileStreamGroup1]
WITH (FILETABLE_COLLATE_FILENAME = SQL_Latin1_General_CP1_CI_AS, FILETABLE_DIRECTORY = N'Toolbox', FILETABLE_PRIMARY_KEY_CONSTRAINT_NAME = [PK__ToolFile__5A5B77D580F02547]);


GO

CREATE TRIGGER [dbo].[UpdateFileToolMap]
   ON  [dbo].[ToolFile]
   AFTER INSERT
AS 
BEGIN

	SET NOCOUNT ON;

    INSERT  INTO ToolFileMap
            ( stream_id ,
              toolid
            )
            SELECT  stream_id ,
                    ( SELECT TOP 1
                                name
                      FROM      ToolFile
                      WHERE     path_locator = ISource.parent_path_locator
                    ) AS toolid
            FROM    INSERTED ISource
            WHERE   is_directory = 0		

END